# -*- coding: utf-8 -*-
"""
Module Description: 根据事务最终提交的数据, 生成相应sql语句的函数
Date: 2018/8/28
Author:Bai Jin Ping
"""

SQL_INSERT_FORMAT = "INSERT INTO {tb_name}({field_name_list}) VALUES {placeholder_list}"
SQL_CDT_DELETE_FORMAT = "DELETE FROM {tb_name} WHERE {condition}"
SQL_PURE_DELETE_FORMAT = "DELETE FROM {tb_name}"
SQL_CDT_UPDATE_FORMAT = "UPDATE {tb_name} SET {set_section} WHERE {condition}"
SQL_PURE_UPDATE_FORMAT = "UPDATE {tb_name} SET {set_section}"


# sql语句中参数使用的占位符( 这里用的是pymysql的占位符 )
PARAMS_PLACEHOLDER = '%s'


def generate_sql_from_transaction_change_list(trans_list, placeholder=PARAMS_PLACEHOLDER):
    """
    根据事务改动内容生成sql列表
    :param trans_list:
        [
            {"orm": "对象名", "pk": "主键名", "action": "insert", "body": {"字段名": 字段值, ...}},
            {"orm": "对象名", "pk": "主键名", "action": "delete", "body": {"字段名__运算符": 字段值, ...}},
            {"orm": "对象名", "pk": "主键名", "action": "update", "body": {"values": {"字段名": 字段值, ...},
            "conditions": {"字段名__运算符": 字段值, ..., "operator": "AND[OR]"}},
            ...
        ]
    :param placeholder: 参数占位符
    :return: sql_list, param_list
    """
    sql_list = []
    param_list = []

    for act in trans_list:
        # 仅支持AND关系, 无需operator
        if 'body' in act and 'operator' in act['body']:
            del act['body']['operator']

        # insert
        if act['action'] == 'insert':
            insert_sql_list, section_param_nest_list = generate_insert_sql(act, placeholder=placeholder)
            sql_list.extend(insert_sql_list)
            param_list.extend(section_param_nest_list)
        # delete
        elif act['action'] == 'delete':
            sql, section_param_list = generate_delete_sql(act, placeholder=placeholder)
            sql_list.append(sql)
            param_list.append(section_param_list)
        # update
        elif act['action'] == 'update':
            sql, section_param_list = generate_update_sql(act, placeholder=placeholder)
            sql_list.append(sql)
            param_list.append(section_param_list)
        else:
            raise ValueError('Unsupport trans action({})'.format(act['action']))

    return sql_list, param_list


def generate_insert_sql(act, placeholder):
    """
    生成插入sql语句
    :param act:
    :param placeholder: 参数占位符
    :return: sql语句列表(list(string)), 参数元组列表(list(tuple))
    """
    tb_name = act['orm']
    insert_fields = act['body']['values']

    # 取第一个插入参数的key作为VALUES
    field_name_list = insert_fields[0].keys()
    placeholder_list = '(' + ((placeholder + ',') * len(field_name_list))[:-1] + ')'

    # 插入的记录数
    insert_num = len(insert_fields)

    single_insert_sql = SQL_INSERT_FORMAT.format(
        tb_name=tb_name,
        field_name_list=','.join(field_name_list),
        placeholder_list=placeholder_list
    )

    sql_list = [single_insert_sql for _ in range(insert_num)]

    param_list = []
    for params in insert_fields:
        param_list.append([params[field] for field in field_name_list])

    return sql_list, param_list


def generate_delete_sql(act, placeholder):
    """
    生成删除sql语句
    :param act:
    :param placeholder: 参数占位符
    :return: sql语句(string), 参数列表(tuple)
    """
    tb_name = act['orm']

    condition_section_sql, param_list = parse_conditions(act['body'], placeholder=placeholder)

    if param_list:
        return SQL_CDT_DELETE_FORMAT.format(tb_name=tb_name, condition=condition_section_sql), param_list
    else:
        return SQL_PURE_DELETE_FORMAT.format(tb_name=tb_name), param_list


def generate_update_sql(act, placeholder):
    """
    生成更新sql语句
    :param act:
    :param placeholder: 参数占位符
    :return: sql语句(string), 参数列表(tuple)
    """
    tb_name = act['orm']

    set_section_field_list = act['body']['values'].keys()
    params_list = [act['body']['values'][field] for field in set_section_field_list]

    set_section_sql = ','.join([('`{field}` = ' + placeholder).format(field=field)
                                for field in set_section_field_list])
    condition_section_sql, cdt_param_list = parse_conditions(act['body']['conditions'], placeholder=placeholder)

    if cdt_param_list:
        params_list.extend(cdt_param_list)
        return SQL_CDT_UPDATE_FORMAT.format(
            tb_name=tb_name,
            set_section=set_section_sql,
            condition=condition_section_sql
        ), params_list
    else:
        return SQL_PURE_UPDATE_FORMAT.format(
            tb_name=tb_name,
            set_section=set_section_sql
        ), params_list


def parse_conditions(conditions, placeholder):
    """
    解析sql语句条件段(目前只支持AND关系)
    :param conditions:
    :param placeholder: 参数占位符
    :return: sql条件语句段(string), 参数列表(tuple)
    """
    section_list = []
    param_list = []

    for k, v in conditions.iteritems():
        if '__' not in k:
            continue
        field, opt = k.split('__')
        field = '`{}`'.format(field)

        if opt == 'eq':
            section = '{} = {}'.format(field, placeholder)
            param_list.append(v)
        elif opt == 'ne':
            section = '{} != {}'.format(field, placeholder)
            param_list.append(v)
        elif opt == 'gt':
            section = '{} > {}'.format(field, placeholder)
            param_list.append(v)
        elif opt == 'gte':
            section = '{} >= {}'.format(field, placeholder)
            param_list.append(v)
        elif opt == 'lt':
            section = '{} < {}'.format(field, placeholder)
            param_list.append(v)
        elif opt == 'lte':
            section = '{} <= {}'.format(field, placeholder)
            param_list.append(v)
        elif opt == 'is':
            section = '{} IS NULL'.format(field)
        elif opt == 'isnot':
            section = '{} IS NOT NULL'.format(field)
        elif opt == 'in':
            if not v:
                # in查询条件有空列表,那么就加上一个绝对否条件吧~
                section = '{} != {}'.format(placeholder, placeholder)
                param_list.extend([1, 1])
            else:
                section = '{} IN ({})'.format(field, ((placeholder + ',') * len(v))[:-1])
                param_list.extend(list([str(e) for e in v]))
        elif opt == 'like':
            section = '{} LIKE {}'.format(field, placeholder)
            param_list.append(v)
        else:
            raise ValueError('Not support opt!')

        section_list.append(section)

    return ' AND '.join(section_list), param_list
